﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class AlternateSkuDA : IAlternateSkuDA
    {
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string DEFAULT_UOM_ID = "@DEFAULT_UOM_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_ALTERNATE_SKU_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ALTERNATE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND ALT_SKU_ID = @ALT_SKU_ID AND VENDOR_ID = @VENDOR_ID ";
        private string SQL_DELETE_ALTERNATE_SKU = " DELETE FROM @_@ALTERNATE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = SKU_ID ";
        private string SQL_DELETE_ONE_ALTERNATE_SKU = " DELETE FROM @_@ALTERNATE_SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND ALT_SKU_ID = @ALT_SKU_ID AND VENDOR_ID = @VENDOR_ID ";
        private string SQL_INSERT_ALTERNATE_SKU = " INSERT INTO @_@ALTERNATE_SKU ( WH_ID, OWNER_ID, SKU_ID, ALT_SKU_ID, VENDOR_ID, PACK_ID, DEFAULT_UOM_ID, TYPE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @SKU_ID, @ALT_SKU_ID, @VENDOR_ID, @PACK_ID, @DEFAULT_UOM_ID, @TYPE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_ALTERNATE_SKU = " SELECT * FROM( SELECT     altSku.ROWID, altSku.WH_ID, altSku.OWNER_ID, altSku.SKU_ID,  altSku.ALT_SKU_ID, altSku.VENDOR_ID, altSku.PACK_ID, altSku.DEFAULT_UOM_ID,   altSku.TYPE, altSku.UDF1, altSku.UDF2, altSku.UDF3, altSku.UDF4,   altSku.UDF5, altSku.UDF6, altSku.UDF7, altSku.UDF8, altSku.UDF9,   altSku.UDF10, altSku.IS_ACTIVE, altSku.CREATED_BY, altSku.CREATED_DATE,  altSku.UPDATED_BY, altSku.UPDATED_DATE, Sku.DESCR  FROM         @_@ALTERNATE_SKU as altSku INNER JOIN  @_@SKU as Sku ON altSku.OWNER_ID = Sku.OWNER_ID AND altSku.SKU_ID = Sku.SKU_ID )  TEMP ";
        private string SQL_UPDATE_ALTERNATE_SKU = " UPDATE @_@ALTERNATE_SKU SET WH_ID = @WH_ID, PACK_ID = @PACK_ID, DEFAULT_UOM_ID = @DEFAULT_UOM_ID, TYPE = @TYPE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND ALT_SKU_ID = @ALT_SKU_ID AND VENDOR_ID = @VENDOR_ID ";
        private string TYPE = "@TYPE";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckAlternateSkuIDUnique(string ownerID, string skuID, string alternateSkuID, string vendorID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@ALT_SKU_ID", alternateSkuID), new SqlParameter("@VENDOR_ID", vendorID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ALTERNATE_SKU_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAlternateSkuIDUnique(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string alternateSkuID, string vendorID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@ALT_SKU_ID", alternateSkuID), new SqlParameter("@VENDOR_ID", vendorID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ALTERNATE_SKU_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteAlternateSku(List<string> ownerID, List<string> skuID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < ownerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteAlternateSku(List<string> ownerID, List<string> skuID, List<string> altSkuID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < altSkuID.Count; j++)
                    {
                        string[] altSkuID_vendorID = altSkuID[j].Split(new char[] { '\r' });
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@ALT_SKU_ID", altSkuID_vendorID[0]), new SqlParameter("@VENDOR_ID", altSkuID_vendorID[1]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteAlternateSku(DataBase dataBase, DbTransaction tran, List<string> ownerID, List<string> skuID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < ownerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteAlternateSku(DataBase dataBase, DbTransaction tran, List<string> ownerID, List<string> skuID, List<string> altSkuID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < altSkuID.Count; j++)
                    {
                        string[] altSkuID_vendorID = altSkuID[j].Split(new char[] { '\r' });
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID[j]), new SqlParameter("@SKU_ID", skuID[j]), new SqlParameter("@ALT_SKU_ID", altSkuID_vendorID[0]), new SqlParameter("@VENDOR_ID", altSkuID_vendorID[1]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllAlternateSku(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllAlternateSku(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public AlternateSkuInfo GetAlternateSkuByID(string ownerID, string skuID, string alternateskuID, string vendorID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND ALT_SKU_ID = @ALT_SKU_ID AND VENDOR_ID = @VENDOR_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@ALT_SKU_ID", alternateskuID), new SqlParameter("@VENDOR_ID", vendorID) };
            AlternateSkuInfo alternateSkuInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    alternateSkuInfo = new AlternateSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["DEFAULT_UOM_ID"].ToString(), reader["TYPE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["Descr"].ToString());
                }
            }
            return alternateSkuInfo;
        }

        public AlternateSkuInfo GetAlternateSkuByID(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string alternateskuID, string vendorID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND ALT_SKU_ID = @ALT_SKU_ID AND VENDOR_ID = @VENDOR_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID), new SqlParameter("@ALT_SKU_ID", alternateskuID), new SqlParameter("@VENDOR_ID", vendorID) };
            AlternateSkuInfo alternateSkuInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                alternateSkuInfo = new AlternateSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["DEFAULT_UOM_ID"].ToString(), reader["TYPE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["Descr"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return alternateSkuInfo;
        }

        public DataSet GetAlternateSkuByQueryList(List<string> sqlWhere, AlternateSkuQueryEntity alternateskuQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!alternateskuQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, alternateskuQuery.CurrentPage, alternateskuQuery.PageSize, alternateskuQuery.SortField, alternateskuQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetAlternateSkuByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, AlternateSkuQueryEntity alternateskuQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!alternateskuQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, alternateskuQuery.CurrentPage, alternateskuQuery.PageSize, alternateskuQuery.SortField, alternateskuQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<AlternateSkuInfo> GetAlternateSkuListByID(string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            List<AlternateSkuInfo> alternateSkuInfo = new List<AlternateSkuInfo>();
            AlternateSkuInfo alternateSku = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    alternateSku = new AlternateSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["DEFAULT_UOM_ID"].ToString(), reader["TYPE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["Descr"].ToString());
                    alternateSkuInfo.Add(alternateSku);
                }
            }
            return alternateSkuInfo;
        }

        public List<AlternateSkuInfo> GetAlternateSkuListByID(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ALTERNATE_SKU.Replace("@_@", whLoginID) + " WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            List<AlternateSkuInfo> alternateSkuInfo = new List<AlternateSkuInfo>();
            AlternateSkuInfo alternateSku = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                alternateSku = new AlternateSkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["DEFAULT_UOM_ID"].ToString(), reader["TYPE"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["Descr"].ToString());
                alternateSkuInfo.Add(alternateSku);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return alternateSkuInfo;
        }

        public int InsertAlternateSku(AlternateSkuInfo alternateSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    alternateSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_AlternateSku_Parameters(alternateSkuInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertAlternateSku(DataBase dataBase, DbTransaction tran, AlternateSkuInfo alternateSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    alternateSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_AlternateSku_Parameters(alternateSkuInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_AlternateSku_Parameters(AlternateSkuInfo alternateSkuInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DEFAULT_UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.UDF1, SqlDbType.VarChar, 20), new SqlParameter(this.UDF2, SqlDbType.VarChar, 20), new SqlParameter(this.UDF3, SqlDbType.VarChar, 20), new SqlParameter(this.UDF4, SqlDbType.VarChar, 20), new SqlParameter(this.UDF5, SqlDbType.VarChar, 20), new SqlParameter(this.UDF6, SqlDbType.VarChar, 20), new SqlParameter(this.UDF7, SqlDbType.VarChar, 20), new SqlParameter(this.UDF8, SqlDbType.VarChar, 20), 
                new SqlParameter(this.UDF9, SqlDbType.VarChar, 20), new SqlParameter(this.UDF10, SqlDbType.VarChar, 20), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(alternateSkuInfo.WhID))
            {
                paramArray[0].Value = alternateSkuInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = alternateSkuInfo.OwnerID;
            paramArray[2].Value = alternateSkuInfo.SkuID;
            paramArray[3].Value = alternateSkuInfo.AltSkuID;
            if (!string.IsNullOrEmpty(alternateSkuInfo.VendorID))
            {
                paramArray[4].Value = alternateSkuInfo.VendorID;
            }
            else
            {
                paramArray[4].Value = "";
            }
            paramArray[5].Value = alternateSkuInfo.PackID;
            if (!string.IsNullOrEmpty(alternateSkuInfo.DefaultUomID))
            {
                paramArray[6].Value = alternateSkuInfo.DefaultUomID;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            paramArray[7].Value = alternateSkuInfo.Type;
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf1))
            {
                paramArray[8].Value = alternateSkuInfo.Udf1;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf2))
            {
                paramArray[9].Value = alternateSkuInfo.Udf2;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf3))
            {
                paramArray[10].Value = alternateSkuInfo.Udf3;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf4))
            {
                paramArray[11].Value = alternateSkuInfo.Udf4;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf5))
            {
                paramArray[12].Value = alternateSkuInfo.Udf5;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf6))
            {
                paramArray[13].Value = alternateSkuInfo.Udf6;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf7))
            {
                paramArray[14].Value = alternateSkuInfo.Udf7;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf8))
            {
                paramArray[15].Value = alternateSkuInfo.Udf8;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf9))
            {
                paramArray[0x10].Value = alternateSkuInfo.Udf9;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(alternateSkuInfo.Udf10))
            {
                paramArray[0x11].Value = alternateSkuInfo.Udf10;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            paramArray[0x12].Value = alternateSkuInfo.IsActive;
            paramArray[0x13].Value = alternateSkuInfo.CreatedBy;
            paramArray[20].Value = alternateSkuInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateAlternateSku(AlternateSkuInfo alternateSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    alternateSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_AlternateSku_Parameters(alternateSkuInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateAlternateSku(DataBase dataBase, DbTransaction tran, AlternateSkuInfo alternateSkuInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    alternateSkuInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_AlternateSku_Parameters(alternateSkuInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ALTERNATE_SKU.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

